package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import db.Dbclose;
import db.Dbconn;
import entity4wms.Entry;
import tools.ResultSetPrinter;

public final class DAOentry {
    static Connection conn = null;
    static PreparedStatement pstmt = null;
    static ResultSet rs = null;

    public static Entry addEntry(Entry entry) throws SQLException, ClassNotFoundException {
        String sql = "insert into entry(gid,uid,edate,enumber) values(?,?,CURRENT_TIME(),?)";
        conn = Dbconn.getConnection();
        pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        pstmt.setInt(1, entry.getGid());
        pstmt.setInt(2, entry.getUid());
        pstmt.setInt(3, entry.getNumber());
        pstmt.executeUpdate();
        rs = pstmt.executeQuery("select * from entry");
        rs.absolute(-1);
        entry.setId(rs.getInt(1));
        
        Dbclose.close(pstmt, conn);
        return entry;
    }


    public static void removeEntry(Entry entry) throws SQLException, ClassNotFoundException{
        String sql = "delete from entry where eid=?";
        conn = Dbconn.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, entry.getGid());
        pstmt.setInt(2, entry.getUid());
        pstmt.setDate(3, entry.getDate());
        pstmt.setInt(4, entry.getNumber());
        pstmt.executeUpdate();
        Dbclose.close(pstmt, conn);
    }

    public static void updateEntry(Entry entry) throws SQLException, ClassNotFoundException{
        String sql = "update entry set gid =?,uid =?,edate =now(),enumber =? where eid=?";
        conn = Dbconn.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, entry.getGid());
        pstmt.setInt(2, entry.getUid());
        pstmt.setInt(3, entry.getNumber());
        pstmt.setInt(4, entry.getId());
        pstmt.executeUpdate();
        Dbclose.close(pstmt, conn);
    }

    public static void queryEntry(Entry entry) throws SQLException, ClassNotFoundException{
        String sql = "select eid as '入库单号',gname as 商品,uname as 操作员,edate as 入库日期,enumber as 入库数 "
            +"from entry,goods,uaccount where entry.gid=goods.gid and entry.uid = uaccount.uid";
        conn = Dbconn.getConnection();
        pstmt = conn.prepareStatement(sql);
        //pstmt.setInt(1, entry.getEid());
        rs = pstmt.executeQuery();
        ResultSetPrinter.printResultSet(rs);
        Dbclose.queryClose(pstmt, rs, conn);
    }
}